package pers.lance.jdbc.dao;

import pers.lance.jdbc.pojo.Book;
import pers.lance.jdbc.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Lance Li
 * @date 7/10/2018 3:11 PM
 * @desc
 */
public class BookDaoImp implements BookDao {
    public List<Book> finAllBooks() {
        String sql = " select  * from book";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            ArrayList<Book> books = new ArrayList<Book>();
            while (rs.next()) {
                Book book = new Book(rs.getInt("id"), rs.getString("name"));
                books.add(book);
            }
            return books;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return null;
    }

    public int addBook(Book book) {
        String sql = "insert into book (id, name) values(seqBook.nextVal,?)";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, book.getName());
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return 0;
    }

    public int updateBook(int id, Book book) {
        String sql = " update book set name = ? where id = ?";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(2, book.getId());
            pstmt.setString(1, book.getName());
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return 0;
    }

    public int deleteBook(int id) {
        String sql = " delete book where id = ?";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return 0;
    }

    public Book loadBook(int id) {
        String sql = " select  * from book where id = ?";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            ArrayList<Book> books = new ArrayList<Book>();
            while (rs.next()) {
                return new Book(rs.getInt("id"), rs.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return null;
    }

    public int[] addBooks(List<Book> books) {
        String sql = "insert into book (id, name) values(seqBook.nextVal,?)";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        try {
            for(Book book : books) {
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, book.getName());
                pstmt.addBatch();
            }
            return pstmt.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return null;
    }
}
